package com.cloudpivot.receivales.excel.service.impl;

import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson.JSONObject;
import com.cloudpivot.receivales.ding.basic.service.impl.HttpAPIServiceForDing;
import com.cloudpivot.receivales.excel.domain.ExcelForBJDTO;
import com.cloudpivot.receivales.excel.domain.ExcelForPresaleDTO;
import com.cloudpivot.receivales.excel.service.ProcessExcelService;
import com.cloudpivot.receivales.excel.view.RspEnumForExcel;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

@Service
@Slf4j
public class ProcessExcelServiceImpl implements ProcessExcelService {

    @Value("${myprops.cpurl}")
    private String CPURL;

    @Autowired
    private HttpAPIServiceForDing httpAPIServiceForDing;

    /**
     * 读取excel内容
     * @param inputStream
     * @return
     */
    @Override
    public Map<String, Object> importExcelForBJ(InputStream inputStream) {
        Map<String, Object> rsqMap = new HashMap<>();
        //定义一个集合用来存储Object数据
        List<ExcelForBJDTO> list = new ArrayList<>();
        try {
            //创建工作薄对象
            Workbook workbook = WorkbookFactory.create(inputStream);
            //校验工作簿信息 为 true 继续
            int flag = checkSheet(workbook);
            if (flag != 1) {
                rsqMap.put("code", flag);
                rsqMap.put("msg", RspEnumForExcel.getValue(flag));
                rsqMap.put("data", null);
                return rsqMap;
            }
            //获取工作薄模版内容
            Sheet sheet = workbook.getSheetAt(0);
            //获取工作表行数
            int sheetRows = sheet.getPhysicalNumberOfRows();
            log.info("获取到excel内容,共计{}行", sheetRows);
            //跳过表头
            for (int i = 1; i < sheetRows; i++) {
                Row row = sheet.getRow(i);
                if (Objects.isNull(row) || row.getCell(0)==null) {
                    log.info("excel表中," + (i + 1) + "行是空的");
                    continue;
                } else {
                    int col = 0;
                    //赋值
                    ExcelForBJDTO excelForBJDTO = new ExcelForBJDTO();
                    Field[] fields = excelForBJDTO.getClass().getDeclaredFields();
                    try {
                        //对象要严格按照顺序
                        for (Field f : fields) {
                            Cell cell = row.getCell(col);
                            String attrName = f.getName();
                            String methodName = attrName.substring(0, 1).toUpperCase() + attrName.substring(1);

                            Method setMethod = ExcelForBJDTO.class.getMethod("set" + methodName, String.class);
                            setMethod.invoke(excelForBJDTO, convertCellValueToString(cell));
                            col++;
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    list.add(excelForBJDTO);
                }
            }
            log.info("导入文件解析成功！--->{}", JSONObject.toJSONString(list));
            rsqMap.put("code", 1);
            rsqMap.put("msg", RspEnumForExcel.getValue(1));
            rsqMap.put("data", list);
            return rsqMap;
        } catch (Exception e) {
            log.info("导入文件解析失败！");
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 解析400 订单 服务
     * @param inputStream
     * @return
     */
    @Override
    public Map<String, Object> importExcelForPresale(InputStream inputStream) {
        Map<String, Object> rsqMap = new HashMap<>();
        //定义一个集合用来存储Object数据
        List<ExcelForPresaleDTO> list = new ArrayList<>();
        try {
            //创建工作薄对象
            Workbook workbook = WorkbookFactory.create(inputStream);
            //校验工作簿信息 为 true 继续
            int flag = checkSheetForPreSale(workbook);
            if (flag != 1) {
                rsqMap.put("code", flag);
                rsqMap.put("msg", RspEnumForExcel.getValue(flag));
                rsqMap.put("data", null);
                return rsqMap;
            }
            //获取工作薄模版内容
            Sheet sheet = workbook.getSheetAt(0);
            //获取工作表行数
            int sheetRows = sheet.getPhysicalNumberOfRows();
            log.info("获取到excel内容,共计{}行", sheetRows);
            //跳过表头
            for (int i = 1; i < sheetRows; i++) {
                Row row = sheet.getRow(i);
                if (Objects.isNull(row) || row.getCell(0)==null) {
                    log.info("excel表中," + (i + 1) + "行是空的");
                    continue;
                } else {
                    int col = 0;
                    //赋值
                    ExcelForPresaleDTO excelForPresaleDTO = new ExcelForPresaleDTO();
                    Field[] fields = excelForPresaleDTO.getClass().getDeclaredFields();
                    try {
                        //对象要严格按照顺序
                        for (Field f : fields) {
                            Cell cell = row.getCell(col);
                            String attrName = f.getName();
                            String methodName = attrName.substring(0, 1).toUpperCase() + attrName.substring(1);

                            Method setMethod = ExcelForPresaleDTO.class.getMethod("set" + methodName, String.class);
                            setMethod.invoke(excelForPresaleDTO, convertCellValueToString(cell));
                            col++;
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    list.add(excelForPresaleDTO);
                }
            }
            log.info("导入文件解析成功！--->{}", JSONObject.toJSONString(list));
            rsqMap.put("code", 1);
            rsqMap.put("msg", RspEnumForExcel.getValue(1));
            rsqMap.put("data", list);
            return rsqMap;
        } catch (Exception e) {
            log.info("导入文件解析失败！");
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public String getFileBase64(String refId) {
        String url = String.format("%sapi/aliyun/download?refId=%s", CPURL, refId);

        String base64 = null;
        try {
            Base64.Encoder encoder = Base64.getEncoder();
            byte[] ret = httpAPIServiceForDing.doGetToBytes(url);
            base64 = encoder.encodeToString(ret);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        return base64;
    }

    /**
     * 工作簿检查 rspnumforexcel
     *
     * @param workbook
     * @return
     */
    private int checkSheet(Workbook workbook) {

        if (workbook.getNumberOfSheets() < 2) {
            return -1;  //数量不对
        }
        Sheet firstSheet = workbook.getSheetAt(0);
        log.info("-----模版名称:{}", firstSheet.getSheetName());
        if (!firstSheet.getSheetName().equals("模版")) {
            return -2;      //模版名称不对
        }
        int rows = firstSheet.getPhysicalNumberOfRows();
        if (rows == 1) {
            return -3; //(只有ip)工作表内容为空
        }
        String firstCell = firstSheet.getRow(0).getCell(0).getStringCellValue();
        if (StringUtils.isBlank(firstCell) || !firstCell.equals("产品编号")) {
            return -4; //表头不存在
        }
        //检查表头结构
        String head = "产品编号,产品名称,规格型号,货号,主计量单位,品牌,类型,备注";
        for (int i = 0; i < 6; i++) {
            String _cell = firstSheet.getRow(0).getCell(i).getStringCellValue();
            if (StringUtils.isBlank(_cell) || !head.contains(_cell)) {
                return -5;
            }
        }
        return 1;
    }

    private int checkSheetForPreSale(Workbook workbook) {
        Sheet firstSheet = workbook.getSheetAt(0);
        log.info("-----模版名称:{}", firstSheet.getSheetName());
        if (!firstSheet.getSheetName().equals("模版")) {
            return -2;      //模版名称不对
        }
        int rows = firstSheet.getPhysicalNumberOfRows();
        if (rows == 1) {
            return -3; //(只有ip)工作表内容为空
        }
        String firstCell = firstSheet.getRow(0).getCell(0).getStringCellValue();
        if (StringUtils.isBlank(firstCell) || !firstCell.equals("产品名称")) {
            return -4; //表头不存在
        }
        //检查表头结构
        String head = "产品名称,数量,单位,规格,货号,价格,备注,产品编号";
        for (int i = 0; i < 6; i++) {
            String _cell = firstSheet.getRow(0).getCell(i).getStringCellValue();
            if (StringUtils.isBlank(_cell) || !head.contains(_cell)) {
                return -5;
            }
        }
        return 1;
    }

    /**
     * cell 类型转换
     *
     * @param cell
     * @return
     */
    private String convertCellValueToString(Cell cell) {
        //1.1、判断单元格的数据是否为空
        if (cell == null) {
            return null;
        }
        //1.2、设置单元格数据的初始值
        String cellValue = null;
        System.out.println("cell类型------>"+cell.getCellStyle().getDataFormat());
        System.out.println("cell类型2------>"+cell.getCellType().toString());
        //1.3、获取单元格数据的类型
        switch (cell.getCellType()) {
            case NUMERIC:
                //1.3.1、获取到单元格数据的格式
                short dataFormat = cell.getCellStyle().getDataFormat();
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = null;
                    //1.3.1.1、处理日期格式，根据不同日期长度去判断
                    switch (dataFormat) {
                        case 14:
                            sdf = new SimpleDateFormat("yyyy/MM/dd");
                            break;
                        case 21:
                            sdf = new SimpleDateFormat("HH:mm:ss");
                            break;
                        case 22:
                            sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                            break;
                    }
                    //1.3.1.2、处理时间格式
                    Date date = cell.getDateCellValue();
                    assert sdf != null;
                    cellValue = sdf.format(date);
                } else if (dataFormat >50 && dataFormat<180) {
                    //1.3.2、处理普通数字格式
                    DecimalFormat format = new DecimalFormat("0.00");
                    double numericCellValue = cell.getNumericCellValue();
                    cellValue = format.format(numericCellValue);
                }
                break;
            case STRING:
                //处理字符串类型
                cellValue = StrUtil.trim(cell.getStringCellValue());
                break;
            case BOOLEAN:
                //处理布尔类型
                boolean booleanCellValue = cell.getBooleanCellValue();
                cellValue = Boolean.toString(booleanCellValue);
                break;
            case FORMULA:
                //处理函数类型
                cellValue = cell.getCellFormula();
                break;
            case ERROR:
                byte errorCellValue = cell.getErrorCellValue();
                cellValue = Byte.toString(errorCellValue);
                break;
            default:
                break;
        }
        return cellValue;
    }

    /**
     * 4、判断sheet页中是否有合并单元格
     *
     * @param sheet sheet
     * @return 返回值
     */
    private boolean hasMerged(Sheet sheet) {
        int numMergedRegions = sheet.getNumMergedRegions();
        if (numMergedRegions > 0) {
            return true;
        } else {
            return false;
        }
    }


}
